package com.core.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.core.dao.CountryDAO;
import com.core.database.Manager;
import com.core.entities.Country;
import com.core.exception.DAOException;

public class CountryDAOImpl implements CountryDAO {
	

		
		private static CountryDAOImpl instance=null;
		public static CountryDAOImpl getInstance(){
			if(instance==null){
				instance=new CountryDAOImpl();
			}
			return instance;
		}
		
		Connection connection = null;

		@Override
		public Country findByName(String name) throws DAOException {
			Country country = new Country();
			try {
				connection = Manager.getConnection();
				String sql = "SELECT *FROM country WHERE name = '" + name + "'";
				PreparedStatement ps = connection.prepareStatement(sql);
				ResultSet rs = ps.executeQuery();
				int count = 0;
				while (rs.next()) {
					count++;
					if (count == 1) {
						country.setId(rs.getInt(1));
						country.setName(rs.getString(2));

						System.out.println(country);
					}
				}

			} catch (SQLException s) {
				s.printStackTrace();
			}
			return country;
		}

		@Override
		public void insert(Country o) throws DAOException {
			Connection connection = null;
			try {
				connection = Manager.getConnection();
				String sql = " INSERT INTO country (name) VALUE (? )";
				PreparedStatement ps = connection.prepareStatement(sql);
				ps.setString(1, o.getName());
				int result = ps.executeUpdate();
				if (result != 1) {
					throw new SQLException("no row inserted!");
				}
			} catch (Exception ex) {
				ex.printStackTrace();
				throw new RuntimeException("Cannot add new Country", ex);
			} finally {
				Manager.closeConnection(connection);
			}
		}

		@Override
		public Country findById(int id) throws DAOException {
			Country country = new Country();
			try {
				connection = Manager.getConnection();
				String sql = "SELECT *FROM country WHERE id = '" + id + "'";
				PreparedStatement ps = connection.prepareStatement(sql);
				ResultSet rs = ps.executeQuery();
				int count = 0;
				while (rs.next()) {
					count++;
					if (count == 1) {
						country.setId(rs.getInt(1));
						country.setName(rs.getString(2));

						System.out.println(country);
					}
				}

			} catch (SQLException s) {
				s.printStackTrace();
			}
			return country;
		}

		@Override
		public List<Country> findAll() throws DAOException {
			
			ArrayList<Country> countryList = new ArrayList<Country>();
			try {
				connection = Manager.getConnection();
				String sql = " SELECT * FROM country ";
				PreparedStatement ps = connection.prepareStatement(sql);
				ResultSet rs = ps.executeQuery();
				while (rs.next()) {
					Country country = new Country();
					country.setId(rs.getInt(1));
					country.setName(rs.getString(2));
					countryList.add(country);
				}
			} catch (SQLException s) {
				s.printStackTrace();
			}
			return countryList;
		}

		@Override
		public void delete(int id) throws DAOException {
			Country country = new Country();
			try {
				connection = Manager.getConnection();
				String sql = " DELETE FROM country WHERE id = '" + id + "'";
				PreparedStatement ps = connection.prepareStatement(sql);
				int result = ps.executeUpdate();
				if (result != 1) {
					ps.setString(1, country.getName());

				}

			} catch (SQLException s) {
				s.printStackTrace();

			}
		}

		@Override
		public void update(Country o) throws DAOException {

			try {
				connection = Manager.getConnection();
				String sql = "UPDATE country SET name = '" + o.getName()
						+ "' WHERE id = " + o.getId();
				PreparedStatement ps = connection.prepareStatement(sql);
				int result = ps.executeUpdate(sql);

				if (result != 1) {
					ps.setString(1, o.getName());

				}

			} catch (SQLException s) {
				s.printStackTrace();
			}
		}

}
